#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto

if [[ $1 == "" ]];then
   db=zx_dws
else
   db=$1
fi

${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
--客户意向小时统计宽表
INSERT INTO ${db}.dws_relationship_hourcount
WITH tmp AS (SELECT id,
                    create_date_time,
                    if(origin_type IN ('NETSERVICE', 'PRESIGNUP'), 'online', 'offline') AS online_offline,
                    if(clue_state = 'VALID_NEW_CLUES', 'new', 'old')                    AS new_or_old,
                    origin_type,
                    area                                                                AS stu_area,
                    itcast_subject_id,
                    itcast_subject_name,
                    itcast_school_id,
                    itcast_school_name,
                    tdepart_id                                                          AS consult_centre_id,
                    tdepart_name                                                        AS consult_centre,
                    dt,
                    ROW_NUMBER()                                                           OVER(PARTITION BY id) AS id_rn
             FROM zx_dwb.dwb_customer_relationship_detail
             WHERE deleted <> 'true')
SELECT create_date_time               AS datetime,
       online_offline,
       new_or_old,
       origin_type,
       stu_area,
       itcast_subject_id,
       itcast_subject_name,
       itcast_school_id,
       itcast_school_name,
       consult_centre_id,
       consult_centre,
       CASE GROUPING(new_or_old, stu_area, itcast_subject_id, itcast_school_id, origin_type, consult_centre_id)
           WHEN 31 THEN 'all'
           WHEN 15 THEN 'area'
           WHEN 23 THEN 'subject'
           WHEN 27 THEN 'school'
           WHEN 29 THEN 'origin'
           WHEN 30 THEN 'centre'
           ELSE 'other' END           AS group_type,
       CAST(CASE GROUPING(new_or_old, stu_area, itcast_subject_id, itcast_school_id, origin_type, consult_centre_id)
                WHEN 31 THEN COUNT(id)
                WHEN 15 THEN COUNT(id)
                WHEN 23 THEN COUNT(id)
                WHEN 27 THEN COUNT(id)
                WHEN 29 THEN COUNT(id)
                WHEN 30 THEN COUNT(id)
                ELSE NULL END AS INT) AS intended_count,
       dt
FROM tmp
WHERE id_rn = 1
GROUP BY
    GROUPING SETS ( (dt, create_date_time, online_offline, new_or_old),
                    (dt, create_date_time, online_offline, new_or_old, stu_area),
                    (dt, create_date_time, online_offline, new_or_old, itcast_subject_id, itcast_subject_name),
                    (dt, create_date_time, online_offline, new_or_old, itcast_school_id, itcast_school_name),
                    (dt, create_date_time, online_offline, new_or_old, origin_type),
                    (dt, create_date_time, online_offline, new_or_old, consult_centre_id, consult_centre)
    );

--客户线索小时统计宽表
INSERT INTO ${db}.dws_clue_hourcount
WITH tmp AS (SELECT c.id,
                    SUBSTRING(c.create_date_time, 1, 13)                                AS date_time,
                    if(origin_type IN ('NETSERVICE', 'PRESIGNUP'), 'online', 'offline') AS online_offline,
                    if(clue_state = 'VALID_NEW_CLUES', 'new', 'old')                    AS new_or_old,
                    if(a.id IS NULL OR a.appeal_status = '2', 1, 0)                     AS is_valid_clue,
                    SUBSTRING(c.create_date_time, 1, 7)                                 AS dt,
                    ROW_NUMBER()                                                           OVER (PARTITION BY C.id) AS id_rn
             FROM zx_dwd.fact_customer_clue c
                      LEFT JOIN zx_dwd.dim_customer_appeal a
                                ON c.customer_relationship_id = a.customer_relationship_first_id
             WHERE c.deleted <> 'true')
SELECT date_time,
       online_offline,
       new_or_old,
       CAST(COUNT(id) AS INT)                                                                AS clue_count,
       CAST(COUNT(if(is_valid_clue = 1, 1, NULL)) AS INT)                                    AS valid_clue_count,
       CAST(COUNT(if(is_valid_clue = 1, 1, NULL)) * 1.00 / COUNT(id) * 100 AS DECIMAL(5, 2)) AS percent_conversion,
       dt
FROM tmp
WHERE id_rn = 1
GROUP BY dt, date_time,
         online_offline,
         new_or_old;

"